import os

import pinyin
from xlrd import open_workbook
from xlutils.copy import copy


def main():
    # 打开文件
    xlsfile = os.path.abspath('./dealerInfo_20180502.xls')
    dist_file = os.path.abspath('./dealerInfo_20180502_updated.xls')
    rb = open_workbook(xlsfile)
    wb = copy(rb)

    # 根据sheet索引或者名称获取sheet内容
    rs = rb.sheet_by_index(0)  # sheet索引从0开始
    ws = wb.get_sheet(0)
    # write_sheet = write_book.sheet_by_index(0)
    for i in range(1, rs.nrows):
        prov = pinyin.get(rs.cell_value(i, 2), '', format='strip')
        city = pinyin.get(rs.cell_value(i, 3), '', format='strip')
        ws.write(i, 2, prov)
        ws.write(i, 3, city)

    wb.save(dist_file)


if __name__ == '__main__':
    main()
